package Demo;

import java.sql.*;
import java.util.Scanner;

public class Demo1 {
    private static Connection conn = null;
    private static Statement stmt = null;
    private static ResultSet set = null;
    private static final Scanner in = new Scanner(System.in);

    public static void printInf(){
        System.out.println("--------登录学生成绩发布系统-----------");
        System.out.println("------------------------------");
        System.out.println("1.录入学生个人信息");
        System.out.println("2.修改考试科目及分值");
        System.out.println("3.录入学生成绩信息");
        System.out.println("4.删除学生成绩信息");
        System.out.println("5.修改学生成绩信息");
        System.out.println("6.按系部，专业，班级汇总统计");
        System.out.println("7.查询不及格人数，最低分，最高分等");
        System.out.println("8.按某一单科成绩打印成绩单");
        System.out.println("9.按照总成绩打印成绩单");
        System.out.println("------------------------------");
        System.out.println("------------------------------");
        System.out.println("请输入数字以选择，输入0退出");
    }

    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String user = "system";
        String password = "123456";
        String url = "jdbc:oracle:thin:@//localhost:1521/orcl";
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        boolean st = true;
        while(st){
            printInf();
            int ch = in.nextInt();
            switch(ch) {
                case 1:
                    System.out.println("----------录入学生信息---------");
                    System.out.println("请输入学号:");
                    String sno = in.next();
                    System.out.println("请输入姓名:");
                    String name = in.next();
                    System.out.println("请输入性别:");
                    String gender = in.next();
                    System.out.println("请输入系别:");
                    String dept = in.next();
                    System.out.println("请输入专业:");
                    String spec = in.next();
                    System.out.println("请输入班级:");
                    String cla = in.next();
                    System.out.println("请输入年龄:");
                    int age = in.nextInt();

                    String sql = "insert into s values('" + sno + "', '" + name + "', '" + gender + "', '" + dept + "', '" + spec + "','" + cla + "','" + age + "')";
                    try {
                        stmt.execute(sql);
                        System.out.println("录入成功!");
                    } catch (SQLException e) {
                        e.printStackTrace();
//                           System.out.println("添加失败!");
                    }
                    break;
                case 2:
                    System.out.print("请输入要修改科目的课程号:");
                    String ecno = in.next();

                    String sql4 = "select * from c where cno = '" + ecno + "'";
                    try {
                        set = stmt.executeQuery(sql4);
                        System.out.println("已找到该科目:");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    try {
                        if (set.next()) {
                            System.out.println("课程号: " + set.getString("cno"));
                            System.out.println("课程名: " + set.getString("cname"));
                            System.out.println("学分: " + set.getString("credit"));
                        }
                    } catch (SQLException e) {
                        System.out.println("查无该课程!");
                        return;
                    }

                    System.out.println("---请重新录入信息---");
                    System.out.print("输入课程号号:");
                    String cno4 = in.next();
                    System.out.print("输入课程名:");
                    String cname4 = in.next();
                    System.out.print("输入学分:");
                    int credit4 = in.nextInt();

                    sql = "update c set cno = '" + cno4 + "', " +
                            "cname = '" + cname4 + "', " +
                            "credit = " + credit4 + "";

                    try {
                        stmt.execute(sql);
                        System.out.println("修改成功!");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;

                case 3:
                    System.out.println("输入学号:");
                    String sno5 = in.next();
                    System.out.println("输入课程号:");
                    String cno5 = in.next();
                    System.out.println("输入成绩:");
                    int grades = in.nextInt();

                    String sql5 = "insert into sc values('" + sno5 + "', '" + cno5 + "', " + grades + ")";

                    try {
                        stmt.execute(sql5);
                        System.out.println("添加成功!");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    break;
                case 4:
                    System.out.print("请输入要删除成绩的学生的学号:");
                    String sno3 = in.next();

                    System.out.print("请输入要删除这名学生课程的课程号:");
                    String cno3 = in.next();

                    String sql3 = "delete from sc where sno = '" + sno3 + "' and cno = '" + cno3 +"'";

                    try {
                        stmt.execute(sql3);
                        System.out.println("删除成功!");
                    } catch (SQLException e) {
                        //e.printStackTrace();
                        System.out.println("删除失败!");
                    }
                    break;
                case 5:
                    System.out.print("请输入要修改成绩的学生的学号:");
                    String esno = in.next();

                    System.out.print("请输入要修改这名学生课程的课程号:");
                    String ecno1 = in.next();

                    String sql2 = "select * from sc where sno = '" + esno + "' and cno='" + ecno1 + "'";
                    try {
                        set = stmt.executeQuery(sql2);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    try {
                        if (set.next()) {
                            System.out.println("已经找到该学生的成绩:");

                        } else {
                            System.out.println("没有该学生的此项成绩!");
                            return;
                        }
                    } catch (SQLException e) {
                        System.out.println("数据错误!");
                    }

                    System.out.println("请重新录入信息");
                    System.out.print("输入学号:");
                    String sno2 = in.next();
                    System.out.print("输入姓名:");
                    String name2 = in.next();
                    System.out.print("输入性别:");
                    String gender2 = in.next();
                    System.out.print("输入系别:");
                    String dept2 = in.next();
                    System.out.print("输入年龄:");
                    int age2 = in.nextInt();

                    sql = "update s set sno = '" + sno2 + "', " +
                            "sname = '" + name2 + "', " +
                            "gender = '" + gender2+ "', " +
                            "dept = '" + dept2 + "', " +
                            "age = " + age2 + " " +
                            "where sno = " + esno + "";

                    try {
                        stmt.execute(sql);
                        System.out.println("修改成功!");
                    } catch (SQLException e) {
                        System.out.println("数据错误!");
                    }
                    break;
                case 6:
                case 7:
                case 8:
                case 9:


            }
        }

    }
}
